package com.lovo.homework7_14ATM;

import java.sql.*;

public class DBUtil {
    /**
     * 创建连接
     *
     * @return
     */
    public Connection getConnection() {
        Connection con = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 2. 创建连接
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/atm?userSSL=true&useUnicode=true&characterEncoding=UTF8&serverTimezone=GMT",
                    "root", "zjf991130.");
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        // 2. 创建连接
        return con;
    }

    /**
     * 关闭函数,用于改变数据库内容
     *
     * @param con
     * @param statement
     */
    public static void close(Connection con, Statement statement) {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (con != null) {
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    /**
     * 重写关闭函数，用于访问数据库内容
     *
     * @param con
     * @param statement
     * @param rs
     */
    public static void close(Connection con, Statement statement, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (con != null) {
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    /**
     * 根据账户和密码查询对象，并返回一个User类，可以用于登录
     *
     * @param username
     * @param pwd
     * @return
     */

    public Users getByUserNameAndPwd(String username, String pwd) {
        Users users = null;
        Connection con = getConnection();
        PreparedStatement statement = null;
        ResultSet rs = null;
        //  String sql = "select * from t_user where u_usename='" + username + "' and u_usepsw='" + pwd + "' ";
        String sql = "select * from t_user where u_usename=? and u_usepsw=?";
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, username);
            statement.setObject(2, pwd);
            rs = statement.executeQuery();
            if (rs.next()) {//这里用 if 而不用 while的原因：因为去匹配时，只用考虑0条与1条记录
                users = new Users();//存在 就去创建一个User对象，然后就可以将数据存进去

                users.setMoney(rs.getInt("u_money"));
                users.setUserName(rs.getString("u_usename"));
                users.setUserPassWord(rs.getString("u_usepsw"));
                users.setId(rs.getInt("u_id"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(con, statement, rs);
        }

        return users;
    }

    /**
     * 根据id查询对象，并返回一个User类，可以用于 查询、改密、存取、转账转账方
     *
     * @param id
     * @return
     */

    public Users getById(int id) {
        Users users = null;
        Connection con = getConnection();
        PreparedStatement statement = null;
        ResultSet rs = null;
        String sql = "select * from t_user where u_id=?";
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1,id);
            rs = statement.executeQuery();
            if (rs.next()) {//这里用 if 而不用 while的原因：因为去匹配时，只用考虑0条与1条记录
                users = new Users();//存在 就去创建一个User对象，然后就可以将数据存进去
                users.setId(rs.getInt("u_id"));
                users.setMoney(rs.getInt("u_money"));
                users.setUserName(rs.getString("u_usename"));
                users.setUserPassWord(rs.getString("u_usepsw"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(con, statement, rs);
        }

        return users;
    }

    /**
     * 根据账户名查询，可用于转账
     *
     * @param username
     * @return
     */
    public Users getByUsername(String username) {
        Connection con = getConnection();
        PreparedStatement statement = null;
        ResultSet rs = null;
        Users users = null;
        String sql = "select * from t_user where u_usename=?";
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1,username);
            rs = statement.executeQuery();
            if (rs.next()) {
                users = new Users();
                users.setId(rs.getInt("u_id"));
                users.setMoney(rs.getInt("u_money"));
                users.setUserName(rs.getString("u_usename"));
                users.setUserPassWord(rs.getString("u_usepsw"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(con, statement, rs);
        }
        return users;
    }

    public void update(Users users) {
        // 1. 创建连接
        Connection con = getConnection();
        PreparedStatement statement = null;
        String sql = "update t_user set u_money='"+users.getMoney()+"',u_usepsw="+users.getUserPassWord()+" where u_id=?";

        try {
            // 2. 获取执行SQL语句对象
            statement = con.prepareStatement(sql);
            statement.setObject(1,users.getId());
            statement.executeUpdate();
            // 3. 执行SQL语句

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 4. 关闭连接
            close(con, statement);
        }
    }

    public void update(Users users1,Users users2) {
        // 1. 创建连接
        Connection con = getConnection();
        PreparedStatement statement = null;
        String sql1 = "update t_user set u_money='"+users1.getMoney()+"',u_usepsw="+users1.getUserPassWord()+" where u_id=?";
        String sql2 = "update t_user set u_money='"+users2.getMoney()+"',u_usepsw="+users2.getUserPassWord()+" where u_id=?";

        try {
            // 2. 获取执行SQL语句对象
            con.setAutoCommit(false);
            statement = con.prepareStatement(sql1);
            statement.setObject(1,users1.getId());
            statement.executeUpdate();
            statement=con.prepareStatement(sql2);
            statement.setObject(1,users2.getId());
            statement.executeUpdate();
            con.commit();
            // 3. 执行SQL语句

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 4. 关闭连接
            close(con, statement);
        }
    }


}
